package com.commodity.Dao.commodityDaoImp;

import com.commodity.Dao.CommodityDao;
import com.commodity.common.Global;
import com.commodity.entity.Commodity;
import com.commodity.entity.CommoditySearchBean;
import com.commodity.utils.PaginateInfo;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.util.StringUtils;

import java.util.ArrayList;
import java.util.List;

public class CommodityDaoImp implements CommodityDao {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(Global.getDataSource());
    RowMapper<Commodity> MAPPER = new BeanPropertyRowMapper<>(Commodity.class);

    @Override
    public List<Commodity> findAll(CommoditySearchBean csb, PaginateInfo pi) {

        String countSql = "select count(0) from t_commodity";
        StringBuilder sb = new StringBuilder();
        List<Object> args = new ArrayList<>();//存储有效参数
        if (StringUtils.hasText(csb.getCommodityId())) {
            sb.append(" and commodity_id = ?");
            args.add(csb.getCommodityId());
        }
        if (StringUtils.hasText(csb.getCommodityName())) {
            sb.append(" and commodity_name like ?");
            args.add("%" + csb.getCommodityName() + "%");
        }
        if (StringUtils.hasText(csb.getCommodityType())) {
            sb.append(" and commodity_type = ?");
            args.add(csb.getCommodityType());
        }
        if (StringUtils.hasText(csb.getSupply())) {
            sb.append(" and supply = ?");
            args.add(csb.getSupply());
        }
        if (csb.getProductionDateFrom() != null) {
            sb.append(" and productionDate >= ?");
            args.add(csb.getProductionDateFrom());
        }
        if (csb.getProductionDateTo() != null) {
            sb.append(" and productionDate < ?");
            args.add(csb.getProductionDateTo());
        }
        if (csb.getPriceFrom() != null) {
            sb.append(" and commodity_price >= ?");
            args.add(csb.getPriceFrom());
        }
        if (csb.getPriceTo() != null) {
            sb.append(" and commodity_price < ?");
            args.add(csb.getPriceTo());
        }
        if (StringUtils.hasText(csb.getWarehouseId())){
            sb.append(" and commodity_id in(select commodity_id from t_inventory where warehouse_id = ? )");
            args.add(csb.getWarehouseId());
        }
        countSql += " where 1=1 " + sb.toString();
        //查询总记录数
        Integer count = jdbcTemplate.queryForObject(countSql, Integer.class, args.toArray());

        System.out.println(count);
        pi.setTotal(count);
        String sql = "select id,commodity_id,commodity_name,commodity_type,commodity_price,supply,productionDate from t_commodity";
        sql += " where 1=1 " + sb.toString() + " limit ?,?";
        args.add(pi.getOffset());
        args.add(pi.getLimit());
        System.out.println(jdbcTemplate.query(sql, MAPPER, args.toArray()));
        return jdbcTemplate.query(sql, MAPPER, args.toArray());
    }

    //保存数据到数据库
    @Override
    public int save(Commodity commodity) {
        String sql = "insert into t_commodity(commodity_id,commodity_name,commodity_type,supply,commodity_price,productionDate) "
                + " values(?,?,?,?,?,?) ";
        return jdbcTemplate.update(sql, commodity.getCommodityId(), commodity.getCommodityName(),
                commodity.getCommodityType(), commodity.getSupply(),
                commodity.getCommodityPrice(), commodity.getProductionDate());
    }
    //根据id删除记录
    @Override
    public int deleteByIds(Integer[] intIds) {
        String sql = "delete from t_commodity where id in(";
        for (int i = 0; i < intIds.length; i++) {
            if (i == intIds.length - 1) {
                sql += "?";
            } else {
                sql += "?,";
            }
        }
        sql += ")";
        return jdbcTemplate.update(sql, intIds);
    }

    @Override
    public Commodity findById(Integer id) {
        String sql = "select id,commodity_id,commodity_name,commodity_type,supply,commodity_price,productionDate from t_commodity where id = ?";
        List<Commodity> commodities = jdbcTemplate.query(sql,MAPPER,id);
        return commodities.isEmpty() ? null : commodities.get(0);
    }

    @Override
    public int update(Commodity commodity) {
        String sql = "update t_commodity set commodity_id = ?,commodity_name = ?,commodity_type = ?,supply =?,commodity_price =?,productionDate =?  where id = ?";
        return jdbcTemplate.update(sql, commodity.getCommodityId(), commodity.getCommodityName(),
                commodity.getCommodityType(), commodity.getSupply(),
                commodity.getCommodityPrice(), commodity.getProductionDate(),commodity.getId());
    }
    public int findByCommodityId(Commodity commodity){
        String sql = "select * from t_commodity where commodity_id = ?";
        List<Commodity> commodityList = jdbcTemplate.query(sql,MAPPER,commodity.getCommodityId());
        return commodityList.size();
    }
}
